Skip to main content

Database Integrations

FF-API-External integrates with multiple database systems to store and retrieve data. This document details the database configuration, connection methods, and utility functions for working with the databases.

Table of Contents​

MySQL Integration​

MySQL Configuration​

MySQL is configured in the db_config.py file:

from app import app
from flask_mysqldb import MySQL
from decouple import config

# main DB
mysql = MySQL()

# MySQL public configurations for DB 'main'
app.config["MYSQL_HOST"] = config('MYSQL_HOST')
app.config["MYSQL_USER"] = config('MYSQL_USER')
app.config["MYSQL_PASSWORD"] = config('MYSQL_PASSWORD')
app.config["MYSQL_DB"] = config('MYSQL_DB')
app.config["MYSQL_CURSORCLASS"] = config('MYSQL_CURSORCLASS')
app.config["MYSQL_PORT"] = int(config('MYSQL_PORT'))

# MySQL configurations
app.config['MYSQL_DATABASE_USER'] = 'con8r'
app.config['MYSQL_DATABASE_PASSWORD'] = 'szitgyk0wn0gg8k3'
app.config['MYSQL_DATABASE_DB'] = 'ndsconfig'
app.config['MYSQL_DATABASE_HOST'] = 'db-mysql-1-do-user-7568415-0.b.db.ondigitalocean.com'
app.config['MYSQL_DATABASE_PORT'] = 25060

mysql.init_app(app)

MySQL Connection​

The MySQL database connection is initialized with the Flask app and accessed via the mysql object. The connection parameters are loaded from environment variables using the python-decouple library.

MySQL Utility Class​

The MySQL utility class in dbutils/mysql.py provides methods for common database operations:

class MySQL:
    def __init__(self):
        self.conn = mysql.connection
        self.cur = self.conn.cursor()
        self.resp = JsonResponse()

    def select(self, select):
        try:
            self.cur.execute(select)
            rows = self.cur.fetchall()
            return self.resp.returnResponse(200, rows)
        except Exception as e:
            return self.resp.returnResponse(400, e)

    def insert(self, insert, val):
        try:
            self.cur.execute(insert, val)
            self.conn.commit()
            return self.resp.returnResponse(200, "Success: "+str(self.cur.rowcount)+" record inserted.")
        except Exception as e:
            return self.resp.returnResponse(400, str(e))

    def update(self, insert, val):
        try:
            self.cur.execute(insert, val)
            self.conn.commit()
            return self.resp.returnResponse(200, "Success: "+str(self.cur.rowcount)+" record updated.")
        except Exception as e:
            return self.resp.returnResponse(400, str(e))

MongoDB Integration​

MongoDB Configuration​

MongoDB connection is configured using environment variables defined in the .env file:

MONGODB_MAIN_PROD=mongodb+srv://starbright:[email protected]/gateway?authSource=admin&replicaSet=db-mongodb-1&tls=true&tlsCAFile=/workspace/mongodb-1-cert.crt
MONGODB_MAIN_DEV_PREFIX=mongodb+srv://starbright:[email protected]/gateway?authSource=admin&replicaSet=db-mongodb-1&tls=true&tlsCAFile=

MongoDB Connection​

MongoDB connections are established using the pymongo library. The connection string is loaded from environment variables using the python-decouple library.

MongoDB Utility Class​

The MongoDB utility class in dbutils/mongodb.py provides comprehensive methods for working with MongoDB collections:

class MongoDB:
    def __init__(self, no_http=False):
        self.no_http = no_http
        try:
            self.mclient = MongoClient(config('MONGODB_MAIN_PROD'))
        except FileNotFoundError:
            currentdir = os.path.dirname(os.path.abspath(inspect.getfile(inspect.currentframe())))
            parentdir = os.path.dirname(currentdir)
            self.mclient = MongoClient(config('MONGODB_MAIN_DEV_PREFIX') + parentdir + "/mongodb-1-cert.crt")
        self.resp = JsonResponse()

Key methods provided by the MongoDB utility class include:

insert_or_update​

Insert a document if it doesn't exist, or update it if it does:

def insert_or_update(self, db, col, filter, update):
    """
    Inserts or updates a document in a MongoDB collection based on the filter.
    If the document exists and is identical to the update, no operation is performed.
    If the document exists but differs, it is updated.
    If the document does not exist, it is inserted.
    """

insert_many​

Insert multiple documents into a collection:

def insert_many(self, db, col, ins):
    """
    Insert multiple documents into a collection.
    """

find_many​

Find multiple documents in a collection:

def find_many(self, db, col, query=None, sortkey=None, sortorder=None, limit=None, skip=0, projection=None):
    """
    Find multiple documents in a collection with sorting, limit, and pagination options.
    """

delete_many​

Delete multiple documents from a collection:

def delete_many(self, db, col, query=None):
    """
    Delete multiple documents from a collection based on a query.
    """

Data Models​

Data models in the FF-API-External service typically correspond to external API services. Models handle the business logic for interacting with these services, while the database utilities handle data persistence.

Example from a model using the MongoDB utility:

class GSMArena:
    def __init__(self):
        self.mdb = MongoDB()
        self.gsm_arena_base_url = "https://www.gsmarena.com/"

    def fetch_store_brand_list(self):
        # Fetch the brand list from the website
        brands = self.get_brand_list()
        
        # Store in MongoDB
        for brand in brands:
            self.mdb.insert_or_update(
                "gateway", "gsmarena_brands",
                {"brand_id": brand['brand_id']},
                brand
            )
        
        return {"message": "Brands fetched and stored successfully"}

Best Practices​

When working with the database integrations in FF-API-External, follow these best practices:

  1. Use environment variables for sensitive credentials:

    • Never hardcode database credentials in your code
    • Use the python-decouple library to load credentials from environment variables

  2. Handle exceptions gracefully:

    • Wrap database operations in try-except blocks
    • Return meaningful error messages
    • Log errors for debugging

  3. Use parameterized queries:

    • Prevent SQL injection by using parameterized queries
    • Pass values as parameters rather than building SQL strings

  4. Close connections when done:

    • In long-running operations, ensure connections are properly closed
    • Use connection pooling where appropriate

  5. Optimize queries:

    • Use proper indexing in MongoDB
    • Limit results when querying large collections
    • Use projections to return only needed fields

  6. Validate data before storage:

    • Validate data types and formats before storing in the database
    • Sanitize user input

  7. Follow collection/table naming conventions:

    • Use consistent naming patterns for collections/tables
    • Document the data schema for each collection/table